﻿using System.Data.SQLite;
using System.IO;

namespace DataFetchTool.Entity
{
    class SQLHelper
    {
        private SQLiteConnection connection;

        public string _ServiceHostCache;

        public SQLHelper()
        {
            this.connection = new SQLiteConnection("Data Source=database.db;Version=3;");
            connection.Open();
            InitSystemSettingTable();
            _ServiceHostCache = ServiceHost;
        }

        private void InitSystemSettingTable()
        {
            FileInfo sqlFile = new FileInfo("database.db");
            if (!sqlFile.Exists)
            {
                SQLiteConnection.CreateFile("database.db");
            }
            string queryString = "CREATE TABLE IF NOT EXISTS FETCH_SETTING (KEY TEXT, KEY_VALUE TEXT)";
            var dbCommand = connection.CreateCommand();
            dbCommand.CommandText = queryString;
            dbCommand.ExecuteNonQuery();
        }

        public string ServiceHost
        {
            get
            {
                if (string.IsNullOrWhiteSpace(_ServiceHostCache))
                {
                    string queryString = "SELECT KEY_VALUE FROM FETCH_SETTING WHERE KEY = 'SERVICE_HOST'";
                    using (var dbCommand = connection.CreateCommand())
                    {
                        dbCommand.CommandText = queryString;
                        using (var dataReader = dbCommand.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                _ServiceHostCache = dataReader.GetValue(0)?.ToString();
                            }
                        }
                        return _ServiceHostCache;
                    }
                }
                return _ServiceHostCache;
            }
            set
            {
                if (string.IsNullOrWhiteSpace(_ServiceHostCache))
                {
                    _ServiceHostCache = value;
                    // INSERT
                    string queryString = @"INSERT INTO FETCH_SETTING VALUES ('SERVICE_HOST', @HOST)";
                    using (var dbCommand = connection.CreateCommand())
                    {
                        dbCommand.CommandText = queryString;
                        dbCommand.Parameters.Add("@HOST", System.Data.DbType.String).Value = value;
                        dbCommand.ExecuteNonQuery();
                    }
                }
                else
                {
                    _ServiceHostCache = value;
                    // UPDATE
                    string queryString = @"UPDATE FETCH_SETTING SET KEY_VALUE = @HOST WHERE KEY = 'SERVICE_HOST'";
                    using (var dbCommand = connection.CreateCommand())
                    {
                        dbCommand.CommandText = queryString;
                        dbCommand.Parameters.Add("@HOST", System.Data.DbType.String).Value = value;
                        dbCommand.ExecuteNonQuery();
                    }
                }
            }
        }

        public void Close()
        {
            if (connection != null)
            {
                connection.Close();
                connection = null;
            }
        }
    }
}
